﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data;

namespace Demo.Model
{

    [Table("Point")]
    public class PointModel
    {
        public long Id { get; set; }
        public float X { get; set; }
        public float Y { get; set; }
        public string Datakey { get; set; }

        public void Insert()
        {
            string strConn = Config.TestDBConn;
            if (string.IsNullOrEmpty(strConn))
            {
                Trace.WriteLine("获取数据库配置失败");
                return;
            }
            using (System.Data.IDbConnection conn = new SqlConnection(strConn))
            {
                conn.Insert(this);
            }
        }

        /// <summary>
        /// 更新poit位置
        /// </summary>
        public void Update()
        {
            string strConn = Config.TestDBConn;
            if (string.IsNullOrEmpty(strConn))
            {
                Trace.WriteLine("获取数据库配置失败");
                return;
            }
            using (System.Data.IDbConnection conn = new SqlConnection(strConn))
            {
                conn.Update(this);
            }
        }

        public void Remove()
        {
            string strConn = Config.TestDBConn;
            if (string.IsNullOrEmpty(strConn))
            {
                Trace.WriteLine("获取数据库配置失败");
                return;
            }
            using (System.Data.IDbConnection conn = new SqlConnection(strConn))
            {
                conn.Delete(this);
            }
        }

        /// <summary>
        /// 将数据库Poit转为Feature
        /// </summary>
        /// <returns></returns>
        public DotSpatial.Data.IFeature ToFeature(DotSpatial.Data.FeatureSet fs)
        {
            DotSpatial.Topology.Coordinate c = new DotSpatial.Topology.Coordinate(this.X, this.Y);
            DotSpatial.Topology.Point pt = new DotSpatial.Topology.Point(c); // Snapping changes

            //直接创建Table有问题，先创建Featureset
            //var table = new DataTable();
            //table.Columns.Add("Datakey", typeof(String));
            //DotSpatial.Data.Feature f = new DotSpatial.Data.Feature(pt);
            // f.DataRow = table.NewRow(); 
            DotSpatial.Data.IFeature f = fs.AddFeature(pt);
            if (!f.DataRow.Table.Columns.Contains("Datakey"))
            {
                f.DataRow.Table.Columns.Add("Datakey", typeof(String));
            }
            if (!f.DataRow.Table.Columns.Contains("FID"))
            {
                f.DataRow.Table.Columns.Add("FID", typeof(int));
            }
            f.DataRow.BeginEdit();
            f.DataRow["Datakey"] = this.Datakey;
            f.DataRow["FID"] = f.Fid;
            f.DataRow.EndEdit();
            return f;
        }

        /// <summary>
        /// 查询数据库所有Point
        /// </summary>
        /// <returns></returns>
        public static List<PointModel> FindAll()
        {
            string strConn = Config.TestDBConn;
            if (string.IsNullOrEmpty(strConn))
            {
                Trace.WriteLine("获取数据库配置失败");
                return null;
            }
            using (System.Data.IDbConnection conn = new SqlConnection(strConn))
            {
                List<PointModel> ptList = conn.GetList<Model.PointModel>().ToList();
                return ptList;
            }
        }

        /// <summary>
        /// 查询所有Feature
        /// </summary>
        /// <returns></returns>
        public static void FindAllFeature(ref DotSpatial.Data.FeatureSet fs)
        {
            List<PointModel> pmList = new List<PointModel>();


            pmList = FindAll();
            if (pmList != null && pmList.Count > 0)
            {
                foreach (var item in pmList)
                {
                    item.ToFeature(fs);
                }
            }
        }

        /// <summary>
        /// 根据Datakey获取数据库Point
        /// </summary>
        /// <param name="datakey"></param>
        /// <returns></returns>
        public static PointModel FindByDatakey(string datakey)
        {
            string strConn = Config.TestDBConn;
            if (string.IsNullOrEmpty(strConn))
            {
                Trace.WriteLine("获取数据库配置失败");
                return null;
            }
            using (System.Data.IDbConnection conn = new SqlConnection(strConn))
            {
                PointModel pt = conn.QueryFirst<Model.PointModel>("select * from Point where Datakey=@Datakey", new { Datakey = datakey });
                return pt;
            }
        }

        public static void Clear()
        {
            string strConn = Config.TestDBConn;
            if (string.IsNullOrEmpty(strConn))
            {
                Trace.WriteLine("获取数据库配置失败");
            }
            using (System.Data.IDbConnection conn = new SqlConnection(strConn))
            {
                conn.Execute("delete from Point");
            }
        }
    }

    [Table("Image")]
    class ImageModel
    {
        public long Id { get; set; }
        public byte[] Value { get; set; }

        public void Insert()
        {
            string strConn = Config.TestDBConn;
            if (string.IsNullOrEmpty(strConn))
            {
                Trace.WriteLine("获取数据库配置失败");
                return;
            }
            using (System.Data.IDbConnection conn = new SqlConnection(strConn))
            {
                conn.Execute("delete from Image");
                conn.Insert(this);
            }
        }

        public static ImageModel Get()
        {
            string strConn = Config.TestDBConn;
            if (string.IsNullOrEmpty(strConn))
            {
                Trace.WriteLine("获取数据库配置失败");
                return null;
            }
            using (System.Data.IDbConnection conn = new SqlConnection(strConn))
            {
                List<ImageModel> imgList = conn.GetList<ImageModel>().ToList();
                if (imgList != null && imgList.Count == 1)
                {
                    return imgList[0];
                }
                return null;
            }
        }
    }
}
